package com.yyz.tools;


import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Excel工具类。
 */
public class ExcelUtil {

    private static final DecimalFormat format = new DecimalFormat("0.###");

    /**
     * Excel文件转List<List<String>>
     *
     * @param is     输入流
     * @param ignore 要跳过的记录数
     */
    public static List<List<String>> readExcel(InputStream is, int ignore) throws IOException, InvalidFormatException {
        Workbook book = WorkbookFactory.create(is);

        Sheet sheet = book.getSheetAt(0);

        List<List<String>> list = new ArrayList<List<String>>();
        Row row1 = sheet.getRow(sheet.getFirstRowNum());
        int min = row1.getFirstCellNum();
        int max = row1.getLastCellNum();
        for (int i = sheet.getFirstRowNum() + ignore; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            List<String> contentList = new ArrayList<String>();
            for (int j = min; j < max; j++) {
                Cell cell = row.getCell(j);
                String stringValue = readCell(cell);
                contentList.add(stringValue);
            }
            list.add(contentList);
        }
        return list;
    }

    /**
     * Excel文件转List<List<String>>
     *
     * @param is 输入流
     */
    public static List<List<String>> readExcel(InputStream is) throws IOException, InvalidFormatException {
        return readExcel(is, 0);
    }

    private static String readCell(Cell cell) {
        if (cell == null) {
            return "";
        }
        int cellType = cell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)){
                    Date d = cell.getDateCellValue();
                    DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                    return formater.format(d);
                }
                return format.format(cell.getNumericCellValue());
            case Cell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            default:
                return "";
        }
    }
}